'''
Author: momochong0
Date: 2021-05-25 00:39:58
LastEditors: momochong0
LastEditTime: 2021-05-25 00:48:27
Description: 要推网络荣誉出品
'''
#coding=utf-8
import urllib2
import jsonpath
import json
import chardet
import time, datetime
import pymysql.cursors

url = 'http://www.bishijie.com/api/news/?size=1'
request = urllib2.Request(url)
response = urllib2.urlopen(request)
html = response.read()

# 把json格式字符串转换成python对象
jsonobj = json.loads(html)

# 从根节点开始，匹配name节点
newsflash_id = jsonpath.jsonpath(jsonobj, '$..newsflash_id')
content = jsonpath.jsonpath(jsonobj, '$..content')
issue_time = jsonpath.jsonpath(jsonobj, '$..issue_time')

#print newsflash_id[0]
content = json.dumps(content[0], ensure_ascii=False)
content = content[1:-1]
#print content
#print issue_time
dateArray = time.localtime(int(issue_time[0]))
otherStyleTime = time.strftime("%Y-%m-%d %H:%M:%S", dateArray)
#print otherStyleTime

sqlstr = "insert into info_news_flash (id,content,create_date) values('%s','%s','%s')" % (
    newsflash_id[0], content, otherStyleTime)
#print sqlstr

conn = pymysql.connect(host='localhost',
                       port=3306,
                       user='root',
                       password='root',
                       db='virtual_coin',
                       charset="utf8",
                       cursorclass=pymysql.cursors.DictCursor)
#conn = pymysql.connect(host='42.51.201.237', port=3306, user='momochong0', password='momochong0', db='virtual_coin',charset="utf8", cursorclass=pymysql.cursors.DictCursor)
#connect()方法用于创建与数据库的连接，里面可以指定参数，这一步只是连接到了数据库，操作数据库还需要下面的游标
cur = conn.cursor()  #通过获取到的conn数据库的cursor方法创建游标

qstr = "select id from info_news_flash where id='%s'" % (newsflash_id[0])
print qstr
print "\r\n"
cur.execute(qstr)
data = cur.fetchone()
if data == None:
    print "查询结果为空，进行数据插入'\r\n'"
    #.decode("utf-8").encode("gb2312")
    #上一行代码跟在上上行后面，windows系统需要,linux不需要
    cur.execute(sqlstr)
    conn.commit()  #提交数据进数据库

cur.close()
conn.close()  #关闭数据库连接
